package com.seal.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.seal.comm.utils.Page;
import com.seal.comm.utils.StringUtils;
import com.seal.dao.BaseDaoImpl;
import com.seal.dao.IMemberInfoDao;
import com.seal.dto.MemberInfoDto;
import com.seal.model.MemberInfo;

@Service("memberInfoDaoImpl")
@Transactional(readOnly = true)
public class MemberInfoDaoImpl extends BaseDaoImpl implements IMemberInfoDao {

	public void addMemberInfo(MemberInfo ca) throws Exception {
		this.save(ca);
	}

	public void updateMemberInfo(MemberInfo ca) throws Exception {
		this.update(ca);
	}

	public void deleteMemberInfo(MemberInfo ca) throws Exception {
		this.delete(ca);
	}

	public MemberInfo getMemberInfoById(Long id) throws Exception {
		String hql = "from MemberInfo t where t.id = ? ";
		return (MemberInfo) this.getSessionFactory().getCurrentSession().createQuery(
				hql).setLong(0, id).uniqueResult();
	}

	public List<MemberInfo> getAllMemberInfo(MemberInfoDto memberDto, Page page) throws Exception {
		StringBuffer hql = new StringBuffer("from MemberInfo t where 1=1");
		if (memberDto.getUserId() !=null) {
			hql.append(" and t.userId = :userId");
		}
		
		if (memberDto.getRelateCompanyid() !=null) {
			hql.append(" and t.relateCompanyid = :relateCompanyid");
		}
		
		if (memberDto.getRelateUserid() !=null) {
			hql.append(" and t.relateUserid = :relateUserid");
		}


		Query query = this.getSession().createQuery(hql.toString());
		query.setProperties(memberDto);
		if (null != page) {
			query.setFirstResult(page.getFirstResult());
			query.setMaxResults(page.getPageSize());
			String countSql = "select count(t) " + hql.toString();

			Query queryCount = this.getSession().createQuery(countSql);
			queryCount.setProperties(memberDto);
			page.setTotalNumber(Integer.parseInt(queryCount.uniqueResult()
					.toString()));
		}
		return query.list();
	}

	public int getAllMemberInfoCount(String userId) throws Exception {
		String countSql = "select count(t) from MemberInfo t where t.userId='" + userId +"'";
		Query queryCount = this.getSession().createQuery(countSql);
		return Integer.parseInt(queryCount.uniqueResult().toString());
	}
	
	public int getNotInGroupMemberInfoCount(String userId) throws Exception {
		String countSql = "select count(1) from member_info m where m.user_id='"
				+ userId
				+ "' and id not in(select distinct member_id from group_member gm where m.user_id='"
				+ userId + "')";
		System.out.println(countSql);
		Query queryCount = this.getSession().createSQLQuery(countSql);
		return Integer.parseInt(queryCount.uniqueResult().toString());
	}
	
	public List<MemberInfo> getNotInGroupMemberInfo(String userId, Page page) throws Exception{
		String selectSql = "select * from member_info m where m.user_id='"
				+ userId
				+ "' and m.id not in(select distinct member_id from group_member gm "
				+ "where EXISTS(select * from group_info gi where gm.group_id=gi.id and gi.user_id='"
				+ userId + "')) order by relate_companyid,relate_userid";
		Query query = this.getSession().createSQLQuery(selectSql);
		if (null != page) {
			query.setFirstResult(page.getFirstResult());
			query.setMaxResults(page.getPageSize());
			page.setTotalNumber(getNotInGroupMemberInfoCount(userId));
		}
		List list = query.list();
		List<MemberInfo> memberList=new ArrayList<MemberInfo>();
		for(Object obj : list){
			Object[] objArr=(Object[])obj;
			MemberInfo member=new MemberInfo();
			member.setId(new Long(objArr[0].toString()));
			member.setCompanyId(new Long(objArr[1].toString()));
			member.setUserId(objArr[2].toString());
			member.setRelateCompanyid(new Long(objArr[3].toString()));
			member.setRelateUserid(objArr[4].toString());
			member.setRelateName(objArr[5].toString());
			member.setBirthday(StringUtils.objToStr(objArr[6]));
			member.setEmail(StringUtils.objToStr(objArr[7]));
			member.setTelephone(StringUtils.objToStr(objArr[8]));
			member.setRemark(StringUtils.objToStr(objArr[9])); 
			memberList.add(member);
		}
		return memberList;
	}
}
